
/*
* Vasanth Tatta 08/10/2003
* This procedure was primarly written for the view orders
* Its modified  to run as a job for the Backlog report
	select * from Backlog_History_Table 
 exec sp_backlog_orders_job
*/

ALTER      proc sp_backlog_orders_job 

as
	-------------------- variable declaretions-----------
	declare @so_id_I int, @li_id_I int, @req_qty_I int, @qty_ship_I int, @li_no_I int, @cust_id_I int, @prod_id_I int
	declare @rev_level_I int, @is_cancel_B bit, @is_expedite_B bit
	declare	@op_hold_V varchar(10), @mk_hold_V varchar(10), @sa_hold_V varchar(10),  @fi_hold_V varchar(10)
	declare @so_no_V varchar(10), @li_status_V varchar(40), @part_no_V varchar(40), @cust_name_V varchar(100)
	declare @desc_V varchar(50), @cust_no_V varchar(50), @cust_po_V varchar(50),  @sales_person_V varchar(50)
	declare @end_user_V varchar(65), @sales_rep_V varchar(50), @backlog_type_V varchar(20)
	declare @unit_cost_F float, @amount_F float
	declare @so_date_DT datetime, @req_dock_date_DT datetime, @sche_date_DT datetime, @ship_date_DT datetime
	declare @rev_ship_date_DT datetime
	declare @Requested_Date_WW_No int,@Requested_Date_Qtr_No int,@Schedule_Date_WW_No int,@Schedule_Date_Qtr_No int,@Revised_Date_WW_No int,@Revised_Date_Qtr_No int
	declare @Booking_Date_WW_No int,@Booking_Date_Qtr_No int
	-------------------- variable declaretions-----------

	select @op_hold_V ='', @mk_hold_V ='',  @sa_hold_V ='', @fi_hold_V =''	
	
	declare MN cursor for
	select distinct salesorder_ID from customer_open_order_lineitem
	open MN
		fetch next from MN into @so_id_I
		while @@fetch_status = 0
		begin

			declare LineItemCursor cursor for
			select LineItem_ID, COOL_Status, COOL_Qty, COOL_Line_Item_No, Product_ID, COOL_Requested_Date, COOL_Schedule_Date, COOL_Revised_Date, COOL_Description, COOL_Unit_Cost, COOL_Amount, COOL_Is_Cancel, COOL_Is_Expedite,Requested_Date_WW_No,Requested_Date_Qtr_No,Schedule_Date_WW_No,Schedule_Date_Qtr_No,Revised_Date_WW_No,Revised_Date_Qtr_No from customer_open_order_lineitem where SalesOrder_ID = @so_id_I
			open  LineItemCursor
			fetch next from LineItemCursor into @li_id_I, @li_status_V, @req_qty_I, @li_no_I, @prod_id_I, @req_dock_date_DT, @sche_date_DT, @rev_ship_date_DT, @desc_V, @unit_cost_F, @amount_F, @is_cancel_B, @is_expedite_B,@Requested_Date_WW_No,@Requested_Date_Qtr_No,@Schedule_Date_WW_No,@Schedule_Date_Qtr_No,@Revised_Date_WW_No,@Revised_Date_Qtr_No
			while @@fetch_status = 0
			begin
				
				select @so_no_V = SalesOrder_No, @cust_id_I = Customer_ID, 
				@so_date_DT= SO_Booking_Date,
				@cust_po_V = SO_Customer_PO_Number, @sales_person_V=SO_Sales_Person, @end_user_V = so_EndUser,
				@sales_rep_V= Regional_Rep,
				@Booking_Date_WW_No = so_Booking_Date_WW_No,@Booking_Date_Qtr_No = So_Booking_Date_Qtr_No  
				from Sales_Order where SalesOrder_ID = @so_id_I
				
				select @rev_level_I = max(ChangeNo) from Change_Sales_Order where SalesOrder_id = @so_id_I
				select @part_no_V = Part_Number from product where product_ID = @prod_id_I
				select @cust_name_V = C_Name, @cust_no_V =C_Number from customer where Customer_ID = @cust_id_I 

				----------set the hold reason codes-----------
				if @li_status_V = 'Hold'
				begin
					-- Prabhanjan, Aug 22, 2004 - Get shipped Qty for Partially Shipped and Remaining Qty Cancelled Line Items
					select @qty_ship_I= isnull(SUM (CSLIT_Qty_Shipped), null) from customer_shipping_line_item_table where LineItem_ID = @li_id_I and CSLIT_Status = 'SHIPPED'

					select @op_hold_V =Hold_Reason_Code from sales_hold_table where SalesOrder_ID = @so_id_I and LineItem_ID = @li_id_I and Hold_By_Group = 'OPERATIONS'
						if @op_hold_V = '0' set @op_hold_V = 'X'
					select @mk_hold_V = Hold_Reason_Code from sales_hold_table where SalesOrder_ID = @so_id_I and LineItem_ID = @li_id_I and Hold_By_Group = 'MARKETING'
						if @mk_hold_V = '0' set @mk_hold_V = 'X'
					select @sa_hold_V = Hold_Reason_Code from sales_hold_table where SalesOrder_ID = @so_id_I and LineItem_ID = @li_id_I and Hold_By_Group = 'SALES'
						if @sa_hold_V = '0' set @sa_hold_V = 'X'
					select @fi_hold_V = Hold_Reason_Code from sales_hold_table where SalesOrder_ID = @so_id_I and LineItem_ID = @li_id_I and Hold_By_Group = 'FINANCE'
						if @fi_hold_V = '0' set @fi_hold_V = 'X'
					select @backlog_type_V = 'HOLD'
										
				end
				----------set the hold reason codes-----------

				else if @li_status_V = 'Approved'		
				begin
	
					select @qty_ship_I= isnull(SUM (CSLIT_Qty_Shipped), null) from customer_shipping_line_item_table where LineItem_ID = @li_id_I and CSLIT_Status = 'SHIPPED'

					if @qty_ship_I is not null
					begin
						if  (@req_qty_I-@qty_ship_I ) >0 
						begin
							set @li_status_V = 'PARTIALLY SHIPPED'
						end
							
						else if (@req_qty_I-@qty_ship_I ) =0 
						begin
							set @li_status_V = ' SHIPPED'
						end
					
					end
					select @backlog_type_V = 'OPEN'
				end		
				
			declare @final_status_V varchar(20)
			if @li_status_V = 'Hold' or @li_status_V = 'Approved' or @li_status_V = 'Partially Shipped'
				set @final_status_V = 'BACKLOG'

			if @qty_ship_I is null set @qty_ship_I = 0
			-----------check if there is a need to update------------------
			-- End_User field inserted into backlog_History_Table, added by Gandhi on 13th Sep 2004
			if @final_status_V = 'BACKLOG'
			begin
				if (select count (LineItem_ID) from Backlog_History_Table where LineItem_ID = @li_id_I and convert(varchar, TimeStamp, 101) = convert(varchar, CURRENT_TIMESTAMP, 101)) =0
 					insert into Backlog_History_Table (SalesOrder_ID, SalesOrder_No,Revison_Level,Customer_ID,Customer_Name,Customer_PO_Number,Customer_Number,Sales_Person,Regional_Rep,
					LineItem_ID,LineItem_No,Product_ID,Part_Number,Sales_Hold,Marketing_Hold,Operations_Hold,Finance_Hold,Req_Dock_Date,
					Booking_Date,Schedule_Date,Revised_Date,BK_Requested_Qty,BK_Unit_Cost,BK_Amount,BK_LineItem_Status,BK_Final_Status,
					BK_Backlog_Type,BK_Description,BK_Is_Cancel,Timestamp,BK_Shipped_Qty,BK_Is_Expedite,End_User,
					Req_Dock_Date_WW_No,Req_Dock_Date_Qtr_No,Schedule_Date_WW_No,Schedule_Date_Qtr_No,Revised_Date_WW_No,Revised_Date_Qtr_No,
					Booking_Date_WW_No,Booking_Date_Qtr_No) 

					values(@so_id_I, @so_no_V, @rev_level_I, @cust_id_I,@cust_name_V, @cust_po_V, @cust_no_V, @sales_person_V, @sales_rep_V, 
					@li_id_I, @li_no_I,@prod_id_I, @part_no_V,  @sa_hold_V, @mk_hold_V, @op_hold_V, @fi_hold_V, 
 					@req_dock_date_DT, @so_date_DT, @sche_date_DT, 
 					@rev_ship_date_DT, @req_qty_I, @unit_cost_F, @amount_F, 
 					@li_status_V, @final_status_V,@backlog_type_V, @desc_V, @is_cancel_B, CURRENT_TIMESTAMP, @qty_ship_I, @is_expedite_B, @end_user_V,
					@Requested_Date_WW_No,@Requested_Date_Qtr_No,@Schedule_Date_WW_No,@Schedule_Date_Qtr_No,@Revised_Date_WW_No,@Revised_Date_Qtr_No,
					@Booking_Date_WW_No,@Booking_Date_Qtr_No)				
 				
	
				-----------check if there is a need to update------------------
				-----------inserting into results table------------------
				else
					Update  Backlog_History_Table set SalesOrder_ID = @so_id_I, SalesOrder_No=@so_no_V, 
					Revison_Level =@rev_level_I, Customer_ID =@cust_id_I, 	Customer_Name =@cust_name_V, 
					Customer_PO_Number =@cust_po_V, Customer_Number= @cust_no_V, Sales_Person=@sales_person_V, Regional_Rep=@sales_rep_V, 
					LineItem_ID =@li_id_I, LineItem_No=@li_no_I, Product_ID=@prod_id_I, Part_Number=@part_no_V,  
					Sales_Hold =@sa_hold_V, Marketing_Hold=@mk_hold_V, Operations_Hold=@op_hold_V, 
					Finance_Hold=@fi_hold_V, Req_Dock_Date=@req_dock_date_DT, Booking_Date=@so_date_DT, 
					Schedule_Date=@sche_date_DT, Revised_Date=@rev_ship_date_DT, BK_Requested_Qty=@req_qty_I, 
					BK_Unit_Cost=@unit_cost_F, BK_Amount=@amount_F, BK_LineItem_Status=@li_status_V, 
					BK_Final_Status=@final_status_V, BK_Backlog_Type=@backlog_type_V,  BK_Description=@desc_V, BK_Is_Cancel= @is_cancel_B, 
					Timestamp =CURRENT_TIMESTAMP, BK_Shipped_Qty = @qty_ship_I, BK_Is_Expedite = @is_expedite_B, End_User = @end_user_V,
					Req_Dock_Date_WW_No = @Requested_Date_WW_No,Req_Dock_Date_Qtr_No = @Requested_Date_Qtr_No,
					Schedule_Date_WW_No = @Schedule_Date_WW_No,Schedule_Date_Qtr_No = @Schedule_Date_Qtr_No,
					Revised_Date_WW_No = @Revised_Date_WW_No,Revised_Date_Qtr_No = @Revised_Date_Qtr_No,
					Booking_Date_WW_No = @Booking_Date_WW_No,Booking_Date_Qtr_No = @Booking_Date_Qtr_No

					where LineItem_ID = @li_id_I and convert(varchar, Backlog_History_Table.TimeStamp, 101) = convert(varchar, CURRENT_TIMESTAMP, 101)
					
			end
				-----------inserting into results table------------------
	
			select  @op_hold_V =NULL,  @mk_hold_V =NULL,  @sa_hold_V =NULL, @fi_hold_V =NULL
			select @final_status_V =null

			select @qty_ship_I = 0

			fetch next from LineItemCursor into @li_id_I, @li_status_V, @req_qty_I, @li_no_I, @prod_id_I, @req_dock_date_DT, @sche_date_DT, @rev_ship_date_DT, @desc_V, @unit_cost_F, @amount_F, @is_cancel_B, @is_expedite_B,@Requested_Date_WW_No,@Requested_Date_Qtr_No,@Schedule_Date_WW_No,@Schedule_Date_Qtr_No,@Revised_Date_WW_No,@Revised_Date_Qtr_No
			end
			close LineItemCursor
			deallocate LineItemCursor
			fetch next from MN into @so_id_I
					
		end
	close MN
	deallocate MN	
 


